package cn.Mmovie.dao.Impl;

import cn.Mmovie.bean.Auditorium;
import cn.Mmovie.bean.Cinema;
import cn.Mmovie.dao.AuditoriumDao;
import cn.Mmovie.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class AuditoriumDaoImpl implements AuditoriumDao {
    @Override
    public Integer insert(Auditorium auditorium) {
        Integer result = -999;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "INSERT INTO auditorium(name,cinema,capacity) " +
                    "VALUES(?,?,?)";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, auditorium.getName());
            psmt.setInt(2, auditorium.getCinema().getId());
            psmt.setInt(3, auditorium.getCapacity());
            result = psmt.executeUpdate();
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Integer delete(Integer id) {
        Integer result = -999;

        try {
            //获取数据库连接对象
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            String sql = "DELETE FROM auditorium WHERE id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            psmt.setInt(1, id);
            //执行操作，返回影响行数
            result = psmt.executeUpdate();
            //释放资源
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return result;
    }

    @Override
    public Integer update(Auditorium auditorium) {
        Integer result = -999;

        try {
            Connection conn = DbUtil.getConnection();
            String sql = "UPDATE auditorium SET name=?,cinema=?,capacity=? WHERE id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setString(1, auditorium.getName());
            psmt.setInt(2, auditorium.getCinema().getId());
            psmt.setInt(3, auditorium.getCapacity());
            psmt.setInt(4, auditorium.getId());
            result = psmt.executeUpdate();
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public Auditorium selectById(Integer id) {
        Auditorium auditorium = null;

        try {
            //获取数据库连接对象
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            String sql = "SELECT id,name,cinema,capacity FROM auditorium WHERE id=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            psmt.setInt(1, id);
            //执行操作，返回查询结果集
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                auditorium = new Auditorium();
                auditorium.setId(rs.getInt("id"));
                auditorium.setName(rs.getString("name"));
                auditorium.setCinema(new Cinema());
                auditorium.getCinema().setId(rs.getInt("cinema"));
                auditorium.setCapacity(rs.getInt("capacity"));
            }
            //释放资源
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return auditorium;
    }

    @Override
    public List<Auditorium> selectAll() {
        List<Auditorium> auditoriums = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "SELECT * FROM auditorium";
            PreparedStatement psmt = conn.prepareStatement(sql);

            ResultSet rs = psmt.executeQuery();
            auditoriums = new ArrayList<>();
            while (rs.next()) {
                Auditorium auditorium=new Auditorium();
                auditorium.setId(rs.getInt("id"));
                auditorium.setName(rs.getString("name"));
                auditorium.setCinema(new Cinema(rs.getInt("cinema")));
                auditorium.setCapacity(rs.getInt("capacity"));
                auditoriums.add(auditorium);
            }
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return auditoriums;
    }

    @Override
    public Integer selectForRecordTotal() {
        Integer result = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "SELECT COUNT(*) FROM auditorium ORDER BY id DESC";
            PreparedStatement psmt = conn.prepareStatement(sql);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
            DbUtil.close(null, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return result;
    }

    @Override
    public List<Auditorium> selectForPageItems(Integer offset, Integer rows, Integer cinemaId) {
        List<Auditorium> auditoriums = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql = "SELECT a.id aid,a.name aname,a.cinema acinema,a.capacity acapacity," +
                    "c.id cid,c.name cname FROM auditorium a LEFT JOIN cinema c ON a.cinema=c.id WHERE c.id=? ORDER BY a.id DESC LIMIT ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, cinemaId);
            psmt.setInt(2, offset);
            psmt.setInt(3, rows);
            ResultSet rs = psmt.executeQuery();
            auditoriums = new ArrayList<>();
            while (rs.next()) {
                Auditorium auditorium = new Auditorium();
                auditorium.setId(rs.getInt("aid"));
                auditorium.setName(rs.getString("aname"));
                auditorium.setCinema(new Cinema());
                auditorium.getCinema().setId(rs.getInt("cid"));
                auditorium.getCinema().setName(rs.getString("cname"));
                auditorium.setCapacity(rs.getInt("acapacity"));
                auditoriums.add(auditorium);
            }
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return auditoriums;
    }

    @Override
    public List<Auditorium> selectForPageItems(Integer offset, Integer rows) {
        List<Auditorium> auditoriums = null;
        try {
            Connection conn = DbUtil.getConnection();
            String sql ="SELECT a.id aid,a.name aname,a.cinema acinema,a.capacity acapacity," +
                    "c.id cid,c.name cname FROM auditorium a LEFT JOIN cinema c ON a.cinema=c.id ORDER BY a.id DESC LIMIT ?,?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            psmt.setInt(1, offset);
            psmt.setInt(2, rows);

            ResultSet rs = psmt.executeQuery();
            auditoriums = new ArrayList<>();
            while (rs.next()) {
                Cinema cinema = new Cinema(rs.getInt("cid"),rs.getString("cname"));
                Auditorium auditorium = new Auditorium();
                auditorium.setId(rs.getInt("aid"));
                auditorium.setName(rs.getString("aname"));
                auditorium.setCinema(cinema);
                auditorium.setCapacity(rs.getInt("acapacity"));
                auditoriums.add(auditorium);
            }
            System.out.println(auditoriums);
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return auditoriums;
    }

    @Override
    public Auditorium selectAuditoriumByName(String name) {
        Auditorium auditorium = null;

        try {
            //获取数据库连接对象
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            String sql = "SELECT id,name,cinema,capacity FROM auditorium WHERE name=?";
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            psmt.setString(1, name);
            //执行操作，返回查询结果集
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                auditorium = new Auditorium();
                auditorium.setId(rs.getInt("id"));
                auditorium.setName(rs.getString("name"));
                auditorium.setCinema(new Cinema());
                auditorium.getCinema().setId(rs.getInt("cinema"));
                auditorium.setCapacity(rs.getInt("capacity"));
            }
            //释放资源
            DbUtil.close(rs, psmt, conn);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return auditorium;
    }

    @Override
    public Integer queryPageNoForAuditoriumId(Integer id, Integer pageSize) {
        Integer pageNo = null;
        try {
            Connection conn = DbUtil.getConnection();
            //创建预处理命令对象
            pageNo = 0;
            Integer pageCount = selectForRecordTotal();
            String sql = "SELECT id,name,cinema,capacity FROM auditorium ORDER BY id DESC LIMIT ?,?";
            Boolean isExits=false;
            PreparedStatement psmt = conn.prepareStatement(sql);
            //设置传入参数
            while (pageNo <= pageCount / pageSize) {
                psmt.setInt(1, pageNo);
                psmt.setInt(2, pageSize);
                //执行操作，返回查询结果集
                ResultSet rs = psmt.executeQuery();
                while (rs.next()) {
                    if (rs.getInt("id") == id) {
                        isExits=true;
                        break;
                    }
                }
                if (isExits){
                    break;
                }
                    pageNo++;
            }
            System.out.println(pageNo);
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return pageNo;
    }
}
